using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using VietStar.Library.Commom.SqlServer;
using Entity = VietStar.Library.Entity;
namespace VietStar.Library.Data
{
	/// <summary>
	/// </summary>
	public class SanPham
{
		public SanPham() {}
				Connection db = new Connection();

/// <summary>
/// Inserts a record into the SanPham table.
/// <summary>
/// <param name="Id"></param>
/// <param name="IdDanhMuc"></param>
/// <param name="TenSanPham"></param>
/// <param name="DonGia"></param>
/// <param name="XuatXu"></param>
/// <param name="Xem"></param>
/// <param name="KhuyenMai"></param>
/// <param name="Hinh"></param>
/// <param name="TrangThai"></param>
/// <param name="MoTa"></param>
/// <returns></returns>
	public bool Insert(Entity.SanPham entity)
	{
		bool boolInsert = false;
		SqlConnection SqlCnn = db.ReadFromWebConfig();
		SqlDataAdapter SqlAdp = new SqlDataAdapter();
		SqlCommand SqlCmd = new SqlCommand();
		SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		try
		{
			SqlCmd.Transaction = SqlTrans;
			SqlCmd.Connection = SqlCnn;
			SqlCmd.CommandText="Proc_SanPham_Insert";
			SqlCmd.CommandType=CommandType.StoredProcedure;			
			SqlCmd.Parameters.Add("@IdDanhMuc", SqlDbType.Int).Value=entity.IdDanhMuc;            
            SqlCmd.Parameters.Add("@LoaiSanPham", SqlDbType.Int).Value = entity.LoaiSanPham;
			SqlCmd.Parameters.Add("@TenSanPham", SqlDbType.NVarChar,100).Value=entity.TenSanPham;
            SqlCmd.Parameters.Add("@TomTatThongTin", SqlDbType.NVarChar, 4000).Value = entity.TomTatThongTin;
			SqlCmd.Parameters.Add("@DonGia", SqlDbType.Float).Value=entity.DonGia;
			SqlCmd.Parameters.Add("@XuatXu", SqlDbType.NVarChar,100).Value=entity.XuatXu;
			SqlCmd.Parameters.Add("@Xem", SqlDbType.Int).Value=entity.Xem;
			SqlCmd.Parameters.Add("@KhuyenMai", SqlDbType.NVarChar,250).Value=entity.KhuyenMai;
			SqlCmd.Parameters.Add("@Hinh", SqlDbType.NVarChar,250).Value=entity.Hinh;
            SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value = entity.TrangThai;
            SqlCmd.Parameters.Add("@MoTa", SqlDbType.NText, 1073741823).Value = entity.MoTa;

			SqlAdp.InsertCommand = SqlCmd;
			SqlAdp.InsertCommand.ExecuteNonQuery();
			SqlCmd.Transaction.Commit();
			boolInsert = true;
			SqlCnn.Close();
		}
		catch
		{
			boolInsert = false;
			SqlCmd.Transaction.Rollback();
		}
		finally
		{
			SqlCmd.Parameters.Clear();
			SqlTrans = null;
		}
		if (SqlCmd != null)
			SqlCmd.Dispose();
		if (SqlAdp != null)
			SqlAdp.Dispose();
		return boolInsert;
	}

/// <summary>
/// Selects a single record from the SanPham table.
/// <summary>
/// <returns>DataTable</returns>
	public DataTable Select()
		{
		DataTable dt = new DataTable();
		SqlConnection SqlCnn = db.ReadFromWebConfig();
		SqlDataAdapter SqlAdp = new SqlDataAdapter();
		SqlCommand SqlCmd = new SqlCommand();
		SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		try
		{
			SqlCmd.Transaction = SqlTrans;
			SqlCmd.Connection = SqlCnn;
			SqlCmd.CommandText="Proc_SanPham_Select";
			SqlCmd.CommandType=CommandType.StoredProcedure;
			SqlAdp.SelectCommand = SqlCmd;
			SqlAdp.SelectCommand.ExecuteNonQuery();
			SqlAdp.Fill(dt);
			SqlCmd.Transaction.Commit();
			SqlCnn.Close();
		}
		catch
		{
			SqlCmd.Transaction.Rollback();
		}
		finally
		{
			SqlCmd.Parameters.Clear();
			SqlTrans = null;
		}
		if (SqlCmd != null)
			SqlCmd.Dispose();
		if (SqlAdp != null)
			SqlAdp.Dispose();
		return dt;
	}

    // Select top 4 sản phẩm khuyến mãi dùng ngoài templates
    public DataTable TemplateSelectTop4KhuyenMai()
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_Templates_SelectTop4KhuyenMai";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }

    // Select top 4 sản phẩm bán chạy dùng ngoài templates
    public DataTable TemplateSelectTop4BanChay()
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_Templates_SelectTop4BanChay";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }

    // Select top 4 sản phẩm xem nhiều dùng ngoài templates
    public DataTable TemplateSelectTop4XemNhieu()
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_Templates_SelectTop4XemNhieu";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }

    // Select top 4 linh kiện dùng ngoài templates
    public DataTable TemplateSelectTop4LinhKien()
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_Templates_SelectTop4LinhKien";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }

    // Select top 4 sản phẩm văn phòng dùng ngoài templates
    public DataTable TemplateSelectTop4SanPhamVanPhong()
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_Templates_SelectTop4SanPhamVanPhong";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }

    // Select top 4 máy văn phòng dùng ngoài templates
    public DataTable TemplateSelectTop4MayVanPhong()
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_Templates_SelectTop4MayVanPhong";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }

/// <summary>
/// Selects a single record from the SanPham table.
/// <summary>
/// <returns>DataTable</returns>
	public DataTable SelectPrimaryKey(Entity.SanPham entity)
	{
		DataTable dt = new DataTable();
		SqlConnection SqlCnn = db.ReadFromWebConfig();
		SqlDataAdapter SqlAdp = new SqlDataAdapter();
		SqlCommand SqlCmd = new SqlCommand();
		SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		try
		{
			SqlCmd.Transaction = SqlTrans;
			SqlCmd.Connection = SqlCnn;
			SqlCmd.CommandText="Proc_SanPham_SelectPrimaryKey";
			SqlCmd.CommandType=CommandType.StoredProcedure;
			SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value=entity.Id;

			SqlAdp.SelectCommand = SqlCmd;
			SqlAdp.SelectCommand.ExecuteNonQuery();
			SqlAdp.Fill(dt);
			SqlCmd.Transaction.Commit();
			SqlCnn.Close();
		}
		catch
		{
			SqlCmd.Transaction.Rollback();
		}
		finally
		{
			SqlCmd.Parameters.Clear();
			SqlTrans = null;
		}
		if (SqlCmd != null)
			SqlCmd.Dispose();
		if (SqlAdp != null)
			SqlAdp.Dispose();
		return dt;
	}
    public DataTable SelectSanPhamByAction(Entity.SanPham entity)
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_SelectSanPhamTheoAction";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.Parameters.Add("@Action", SqlDbType.NVarChar ,20).Value = entity.action;
            SqlCmd.Parameters.Add("@IdMenu", SqlDbType.Int).Value = entity.IdDanhMuc;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }
    public DataTable SelectSanPhamTimKiemByOpt(Entity.SanPham entity)
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_TimKiemByOpt";
            SqlCmd.CommandType = CommandType.StoredProcedure;           
            SqlCmd.Parameters.Add("@IdDanhMuc", SqlDbType.Int).Value = entity.IdDanhMuc;
            SqlCmd.Parameters.Add("@GiaThapNhat", SqlDbType.Float).Value = entity.DonGiaThapNhat;
            SqlCmd.Parameters.Add("@GiaCaoNhat", SqlDbType.Float).Value = entity.DonGiaCaoNhat;
            SqlCmd.Parameters.Add("@KeySearch", SqlDbType.NVarChar, 100).Value = entity.TenSanPham;
            SqlCmd.Parameters.Add("@Option", SqlDbType.Int).Value = entity.Option;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }
    public DataTable SelectTimKiem(Entity.SanPham entity)
    {
        DataTable dt = new DataTable();
        SqlConnection SqlCnn = db.ReadFromWebConfig();
        SqlDataAdapter SqlAdp = new SqlDataAdapter();
        SqlCommand SqlCmd = new SqlCommand();
        SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
        try
        {
            SqlCmd.Transaction = SqlTrans;
            SqlCmd.Connection = SqlCnn;
            SqlCmd.CommandText = "Proc_SanPham_TimKiem";
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.Parameters.Add("@IdDanhMuc", SqlDbType.Int).Value = entity.IdDanhMuc;
            SqlCmd.Parameters.Add("@Option", SqlDbType.Int).Value = entity.Option;
            SqlCmd.Parameters.Add("@KeySearch", SqlDbType.NVarChar, 100).Value = entity.TenSanPham;
            SqlAdp.SelectCommand = SqlCmd;
            SqlAdp.SelectCommand.ExecuteNonQuery();
            SqlAdp.Fill(dt);
            SqlCmd.Transaction.Commit();
            SqlCnn.Close();
        }
        catch
        {
            SqlCmd.Transaction.Rollback();
        }
        finally
        {
            SqlCmd.Parameters.Clear();
            SqlTrans = null;
        }
        if (SqlCmd != null)
            SqlCmd.Dispose();
        if (SqlAdp != null)
            SqlAdp.Dispose();
        return dt;
    }

/// <summary>
/// Updates a record in the SanPham table.
/// <summary>
/// <param name="Id"></param>
/// <param name="IdDanhMuc"></param>
/// <param name="TenSanPham"></param>
/// <param name="DonGia"></param>
/// <param name="XuatXu"></param>
/// <param name="Xem"></param>
/// <param name="KhuyenMai"></param>
/// <param name="Hinh"></param>
/// <param name="TrangThai"></param>
/// <param name="MoTa"></param>
	public bool Update(Entity.SanPham entity)
	{
		bool boolUpdate = false ;
		SqlConnection SqlCnn = db.ReadFromWebConfig();
		SqlDataAdapter SqlAdp = new SqlDataAdapter();
		SqlCommand SqlCmd = new SqlCommand();
		SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		try
		{
			SqlCmd.Transaction = SqlTrans;
			SqlCmd.Connection = SqlCnn;
			SqlCmd.CommandText="Proc_SanPham_Update";
			SqlCmd.CommandType=CommandType.StoredProcedure;
			SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value=entity.Id;
			SqlCmd.Parameters.Add("@IdDanhMuc", SqlDbType.Int).Value=entity.IdDanhMuc;            
            SqlCmd.Parameters.Add("@LoaiSanPham", SqlDbType.Int).Value = entity.LoaiSanPham;
			SqlCmd.Parameters.Add("@TenSanPham", SqlDbType.NVarChar,100).Value=entity.TenSanPham;
            SqlCmd.Parameters.Add("@TomTatThongTin", SqlDbType.NVarChar, 4000).Value = entity.TomTatThongTin;
			SqlCmd.Parameters.Add("@DonGia", SqlDbType.Float).Value=entity.DonGia;
			SqlCmd.Parameters.Add("@XuatXu", SqlDbType.NVarChar,100).Value=entity.XuatXu;
			SqlCmd.Parameters.Add("@Xem", SqlDbType.Int).Value=entity.Xem;
			SqlCmd.Parameters.Add("@KhuyenMai", SqlDbType.NVarChar,250).Value=entity.KhuyenMai;
			SqlCmd.Parameters.Add("@Hinh", SqlDbType.NVarChar,250).Value=entity.Hinh;
			SqlCmd.Parameters.Add("@TrangThai", SqlDbType.Bit).Value=entity.TrangThai;
            SqlCmd.Parameters.Add("@MoTa", SqlDbType.NText, 1073741823).Value = entity.MoTa;

			SqlAdp.UpdateCommand = SqlCmd;
			SqlAdp.UpdateCommand.ExecuteNonQuery();
			SqlCmd.Transaction.Commit();
			boolUpdate = true ;
			SqlCnn.Close();
		}
		catch
		{
			boolUpdate = false ;
			SqlCmd.Transaction.Rollback();
		}
		finally
		{
			SqlCmd.Parameters.Clear();
			SqlTrans = null;
		}
		if (SqlCmd != null)
			SqlCmd.Dispose();
		if (SqlAdp != null)
			SqlAdp.Dispose();
		return boolUpdate;
	}

/// <summary>
		/// Deletes a record from the SanPham table by a composite primary key.
/// <summary>
	public bool Delete(Entity.SanPham entity)
	{
		bool boolDelete = false;
		SqlConnection SqlCnn = db.ReadFromWebConfig();
		SqlDataAdapter SqlAdp = new SqlDataAdapter();
		SqlCommand SqlCmd = new SqlCommand();
		SqlTransaction SqlTrans = SqlCnn.BeginTransaction();
		try
		{
			SqlCmd.Transaction = SqlTrans;
			SqlCmd.Connection = SqlCnn;
			SqlCmd.CommandText="Proc_SanPham_Delete";
			SqlCmd.CommandType=CommandType.StoredProcedure;
			SqlCmd.Parameters.Add("@Id", SqlDbType.Int).Value=entity.Id;

			SqlAdp.DeleteCommand = SqlCmd;
			SqlAdp.DeleteCommand.ExecuteNonQuery();
			SqlCmd.Transaction.Commit();
			boolDelete = true ;
			SqlCnn.Close();
		}
		catch
		{
			boolDelete = false ;
			SqlCmd.Transaction.Rollback();
		}
		finally
		{
			SqlCmd.Parameters.Clear();
			SqlTrans = null;
		}
		if (SqlCmd != null)
			SqlCmd.Dispose();
		if (SqlAdp != null)
			SqlAdp.Dispose();
		return boolDelete;
	}
	}
}
